/*
 * DBProc.java
 *
 * Created on March 8, 2007, 4:05 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */
package com.rockville.ufone;

import java.net.URLEncoder;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;

import org.apache.log4j.Logger;

import com.rockville.ufone.exceptions.ConnectionNotFoundException;

/**
 *
 * @author tazzix
 * @modified Usman
 */
public class DBProc {

    private static final Logger log = Logger.getLogger(DBProc.class);
    private static DBProc dbProc = null;
    ConnectionPool mysqlCp = null;
    ConnectionPool sqlServerCp = null;
    ConnectionPool cp_us_mysql = null;

    /** Creates a new instance of DBProc */
    private DBProc() {
    }

    public static DBProc getInstance() {
        if (dbProc == null) {
            dbProc = new DBProc();
        }
        return dbProc;
    }
//get US server MySql Connection

    public Connection getUSMySQLConnection() throws ConnectionNotFoundException {
        Connection conn = null;
        try {
            if (cp_us_mysql == null) {
                cp_us_mysql = new ConnectionPool("com.mysql.jdbc.Driver", "jdbc:mysql://70.33.254.33:3306/etisalat?useUnicode=true&characterEncoding=UTF-8", "root", "iceland", 1, 1, true);
            }
            conn = cp_us_mysql.getConnection();
            
        } catch (SQLException ex) {
            throw new ConnectionNotFoundException(ex);
        }
        return conn;
    }
    //MySql Connectivity

    public Connection getMySQLConnection() throws ConnectionNotFoundException {
        Connection conn = null;
        try {
            if (mysqlCp == null) {
                mysqlCp = new ConnectionPool("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/ufone?useUnicode=true&characterEncoding=UTF-8", "root", "iceland", 1, 2, true);
            }
            conn = mysqlCp.getConnection();
            /*
            //--Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver" );
            Class.forName("com.mysql.jdbc.Driver");
            //			 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/telenor","root","iceland");
            conn = DriverManager.getConnection("jdbc:mysql://10.5.3.16:3306/telenor","telenor","iceland");
             */
            /*--
            Context initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:/comp/env");
            DataSource ds = (DataSource) envContext.lookup("jdbc/TelenorDB");
            conn = ds.getConnection();
             */
//                } catch(ClassNotFoundException ex){
//                	throw new ConnectionNotFoundException(ex);
        } catch (SQLException ex) {
            throw new ConnectionNotFoundException(ex);
        }
        return conn;
    }

    //SQL Server Connectivity
    public Connection getSQLServerConnection() throws ConnectionNotFoundException {
        Connection conn = null;
        try {
            if (sqlServerCp == null) {
                // this ip 172.16.13.76 has been changed with 172.16.13.106 / rock1.ufone.com
                sqlServerCp = new ConnectionPool("com.microsoft.sqlserver.jdbc.SQLServerDriver", "jdbc:sqlserver://rock1.ufone.com;databaseName=SIMTOOLKIT", "sa", "rock", 1, 2, true);
            }
            conn = sqlServerCp.getConnection();
            /*
            //--Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver" );
            Class.forName("com.mysql.jdbc.Driver");
            //			 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/telenor","root","iceland");
            conn = DriverManager.getConnection("jdbc:mysql://10.5.3.16:3306/telenor","telenor","iceland");
             */
            /*--
            Context initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:/comp/env");
            DataSource ds = (DataSource) envContext.lookup("jdbc/TelenorDB");
            conn = ds.getConnection();
             */
//                } catch(ClassNotFoundException ex){
//                	throw new ConnectionNotFoundException(ex);
        } catch (SQLException ex) {
            throw new ConnectionNotFoundException(ex);
        }
        return conn;
    }

    public void closeMySQLConnection(Connection conn) {
        if (conn != null) {
            try {
//                conn.close();
//    			if(mysqlCp == null) {
//    				mysqlCp = new ConnectionPool("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/telenor", "telenor","iceland", 1, 2, true);
//    			}
                if (mysqlCp != null) {
                    mysqlCp.free(conn);
                }
            } catch (Exception e) {
                log.error("", e);
            }
        }
    }

    public void closeUSMySQLConnection(Connection conn) {
        if (conn != null) {
            try {
                if (cp_us_mysql != null) {
                    cp_us_mysql.free(conn);
                }
            } catch (Exception e) {
                log.error("", e);
            }
        }
    }

    public void closeSQLServerConnection(Connection conn) {
        if (conn != null) {
            try {
//                conn.close();
//    			if(mysqlCp == null) {
//    				mysqlCp = new ConnectionPool("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/telenor", "telenor","iceland", 1, 2, true);
//    			}
                if (sqlServerCp != null) {
                    sqlServerCp.free(conn);
                }
            } catch (Exception e) {
                log.error("", e);
            }
        }
    }

    /**
     * Fetch All Feeds
     *
     * @param strLimit
     * @return
     */
    public Vector<String[]> fetchAllFeeds() {
        Vector<String[]> $v = new Vector<String[]>();
        Connection con = null;
        try {
            con = getMySQLConnection();
            PreparedStatement pst = con.prepareStatement("SELECT id, name, feedurl, ttl FROM rsschannels where feedurl is not null and isactive=1");
//			pst.setInt(1, Integer.parseInt(strLimit));
//			log.debug("FETCH Query =" + pst);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                String[] feed = new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4)};
                $v.add(feed);
            }
            pst.close();

        } catch (Exception e) {
            log.error("Fetch All Feeds", e);
        } finally {
            closeMySQLConnection(con);
        }
        return $v;
    }

    public Date saveNews(String news_headng, String news_Detail, String news_date, String channelId, String news_image) {
        Date newsDate = null;
        SimpleDateFormat sdf = new SimpleDateFormat("EEE MMM dd, yyyy HH:mm:ss");
        try {
            newsDate = sdf.parse(news_date);
        } catch (Exception e) {
            sdf.applyPattern("yyyy,MM,dd h:mm:ss");
            try {
                newsDate = sdf.parse(news_date);
            } catch (Exception e1) {
            }
        }
        if (newsDate == null) {
            newsDate = new Date();
        }
        return saveNews(news_headng, news_Detail, newsDate, channelId, news_image);
    }

    public Date getChannelLastUpdateDate(String channelId) {
        Date lastUpdate = null;
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = getMySQLConnection();
            pst = con.prepareStatement("SELECT lastupdate FROM rsschannels WHERE id=? LIMIT 1");
            pst.setInt(1, Integer.parseInt(channelId));
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                lastUpdate = new Date(rs.getTimestamp(1).getTime());
                log.debug("lastupdate for channel: " + channelId + " is " + new SimpleDateFormat("EEE MMM dd, yyyy HH:mm:ss").format(lastUpdate));
            }
            rs.close();
            pst.close();
            pst = null;
        } catch (Exception e) {
            log.error("getChannelLastUpdateDate(" + channelId + ") ", e);
        } finally {
            closeMySQLConnection(con);
        }
        return lastUpdate;
    }

    public Date saveNews(String news_headng, String news_Detail, Date newsDate, String channelId, String news_image) {

        Date lastNewsDate = null;
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = getMySQLConnection();
            String sql = "";
//				sql = "insert into rss_items (headline, details, imgpath, newsdate, channel_id) values (_utf8 ?, _utf8 ?, ?, ?, ?) " +
//						"ON DUPLICATE KEY " +
//						"update details=_utf8 ?, imgpath=?, newsdate=?";
            sql = "insert into rss_items (headline, details, imgpath, newsdate, channel_id) values (?, ?, ?, ?, ?) "
                    + "ON DUPLICATE KEY "
                    + "update details=?, imgpath=?, newsdate=?";
            pst = con.prepareStatement(sql);
            int setIndex = 1;
            news_headng = news_headng.replaceAll("\'", "''");
            news_Detail = news_Detail.replaceAll("\'", "''");
//				String headingUTF8 = new String(news_headng.getBytes("UTF-8"),"UTF-8");
//				String headingUTF8 = URLEncoder.encode(news_headng,"UTF-8");
//				Charset utf8charset = Charset.forName("UTF-8");  
//				ByteBuffer outputBuffer = utf8charset.encode(news_headng);  
//				byte[] outputData = outputBuffer.array();  
//				news_headng = new String(outputData, "UTF-8");

            pst.setString(setIndex++, news_headng);
            pst.setString(setIndex++, news_Detail);
            pst.setString(setIndex++, news_image);
            pst.setTimestamp(setIndex++, new java.sql.Timestamp(newsDate.getTime()));
            pst.setInt(setIndex++, Integer.parseInt(channelId));

            pst.setString(setIndex++, news_Detail);
            pst.setString(setIndex++, news_image);
            pst.setTimestamp(setIndex++, new java.sql.Timestamp(newsDate.getTime()));

            int count = pst.executeUpdate();
            pst.close();
            /*
            if (count == 0 && updateOnly) {
            sql = "Insert into rss_items(headline, details, imgpath, newsdate, channel_id) values(?,?,?,?,?)";
            pst = con.prepareStatement(sql);
            setIndex = 1;
            pst.setString(setIndex++, news_headng.replaceAll("\'", "''"));
            pst.setString(setIndex++, news_Detail.replaceAll("\'", "''"));
            pst.setString(setIndex++, news_image);
            pst.setTimestamp(setIndex++, new java.sql.Timestamp(newsDate.getTime()));
            pst.setInt(setIndex++, Integer.parseInt(channelId));
            count = pst.executeUpdate();
            pst.close();
            }
             */

        } catch (Exception e) {
            log.error("SaveNews(" + news_headng + ", " + news_Detail + ", " + newsDate + ", " + channelId + ") ", e);
        } finally {
            closeMySQLConnection(con);
        }
        return newsDate;
    }

    /*
    public boolean isNewsExists(String news_headng, String channelId) {
    boolean result = false;
    Connection con = null;
    PreparedStatement pst = null;
    try {
    String sql = "select channel_id from rss_items where headline=? and channel_id=?";
    con = getMySQLConnection();
    pst = con.prepareStatement(sql);
    pst.setString(1, news_headng.replaceAll("\'", "''"));
    pst.setInt(2, Integer.parseInt(channelId));
    ResultSet rs = pst.executeQuery();
    result = rs.next();
    } catch (Exception e) {
    log.error("isNewsExists(" + news_headng + ", " + channelId + ") ", e);
    } finally {
    closeMySQLConnection(con);
    }
    return result;
    }
     */
    public void updateChannelLastNewsDate(String channelId, Date newsDate) {
        if (newsDate == null) {
            return;
        }
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = getMySQLConnection();
            pst = con.prepareStatement("UPDATE rsschannels SET lastupdate=? where id=?");
            pst.setTimestamp(1, new java.sql.Timestamp(newsDate.getTime()));
            pst.setInt(2, Integer.parseInt(channelId));
            pst.executeUpdate();
            pst.close();
            log.info("(" + channelId + ") lastupdate date set to: " + newsDate);
        } catch (Exception e) {
            log.error("updateChannelLastNewsDate(" + channelId + ", " + newsDate + ") ", e);
        } finally {
            closeMySQLConnection(con);
        }
    }

    public int getLatestSimToolkitContentId(String category) {
        int id = 0;
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = getMySQLConnection();
            if ("BORN TODAY".equals(category)) {
                pst = con.prepareStatement("SELECT count(*) as counts FROM quotesnjokes where Category=? and Status=1 and date_format(contentdate,'%Y-%m-%d')=current_date();");
            } else {
                pst = con.prepareStatement("SELECT max(contentId) as contentId FROM quotesnjokes where Category=? and Status=1");
            }
            pst.setString(1, category);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                id = rs.getInt(1);
            }
            rs.close();
            log.info("Latest ID found (" + id + ") for category: " + category);
        } catch (Exception e) {
            log.error("Latest ID found (" + id + ") for category: " + category, e);
        } finally {
            try {
                pst.close();
            } catch (Exception e) {
            }
            closeMySQLConnection(con);
        }
        return id;
    }

    public Vector<String[]> fetchSimToolkitData(String category, int contentId, Date lastNewsDate) {
        Vector<String[]> vec = new Vector<String[]>();
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = getSQLServerConnection();
            pst = conn.prepareStatement("Select contentid, ContentData, ContentData_UR, saveDate from QUOTESnJOKES WHERE Category=? AND contentid > ? " + (lastNewsDate != null ? " and CONVERT(VARCHAR(19), SAVEDATE, 120) > ?" : "") + " AND Status = 1 order by contentid asc, saveDate asc");
            pst.setString(1, category);
            pst.setInt(2, contentId);
            if (lastNewsDate != null) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String newsDate = sdf.format(lastNewsDate);
                pst.setString(3, newsDate);
            }
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                String[] data = new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4)};
                vec.add(data);
            }
            rs.close();
            log.info("Latest SimToolkitData records found (" + vec.size() + ") for category: " + category);
        } catch (Exception e) {
            log.error("getSimToolkitData(" + category + ", " + contentId + ")", e);
        } finally {
            try {
                pst.close();
            } catch (Exception e) {
            }
            closeSQLServerConnection(conn);
        }
        return vec;
    }

    public Vector<String[]> getBornToday() {
        Vector<String[]> vec = new Vector<String[]>();
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = getSQLServerConnection();
            pst = conn.prepareStatement("Select ContentData, ContentData_UR, ContentDate from QUOTESnJOKES WHERE Category='BORN TODAY' AND CONVERT(varchar,ContentDate, 112) = CONVERT(varchar,getDate(), 112) AND Status = 1");
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {

                String contentData = rs.getString(1);
                String contentDataUr = rs.getString(2);

                if (contentData.startsWith("Born Today")) {
                    contentData = contentData.replace("Born Today!%0A", "");
                }
                if (contentDataUr.startsWith("Born Today")) {
                    contentDataUr = contentDataUr.replace("Born Today!%0A", "");
                }

                String[] data = new String[]{contentData, contentDataUr, rs.getString(3)};
                vec.add(data);
            }
            rs.close();
            log.info(" -- BornToday found -- ");
        } catch (Exception e) {
            log.error("BornToday found - ", e);
        } finally {
            try {
                pst.close();
            } catch (Exception e) {
            }
            closeSQLServerConnection(conn);
        }
        return vec;
    }

    public boolean persistSimToolkitData(String category, Vector<String[]> vec) {
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = getMySQLConnection();
            pst = conn.prepareStatement("insert into quotesnjokes (Category,ContentId,ContentData,ContentData_UR,Status,ContentDate,SaveDate,ContentProvider) values (?,?,?,?,1,?,now(),'Rockville')");
            boolean dataFound = false;
            for (String[] data : vec) {
                String contentId = "", content = "", contentUr = "", date = "";
                if (data.length == 3) {
                    content = data[0];
                    contentUr = data[1];
                    date = data[2];
                    dataFound = true;

                    pst.setString(1, category);
                    pst.setString(2, "0");
                    pst.setString(3, content);
                    pst.setString(4, contentUr);
                    pst.setString(5, date);
                    pst.executeUpdate();

                } else if (data.length == 4) {
                    contentId = data[0];
                    content = data[1];
                    contentUr = data[2];
                    date = data[3];
                    dataFound = true;

                    pst.setString(1, category);
                    pst.setString(2, contentId);
                    pst.setString(3, content);
                    contentUr = URLEncoder.encode(contentUr, "UTF-8");
                    pst.setString(4, contentUr);
                    pst.setString(5, date);
                    pst.executeUpdate();
                }
            }
//	        if(dataFound) {
//	        	int[] count = pst.executeBatch();
//	        	log.info("Data Insert Count="+count.length+" -- Input Vector Count="+vec.size()+" -- ");
//	        }
//	        else {
//	        	log.info("No Data Inserted for Category="+category);
//	        }

        } catch (Exception e) {
            log.error("persistSimToolkitData(" + category + ") ", e);
        } finally {
            try {
                pst.close();
            } catch (Exception e) {
            }
            closeMySQLConnection(conn);
        }
        return true;
    }

    public void updateWeatherData(String city, String headline, String details) {
        Connection con = null;
        CallableStatement cs = null;
        try {
            con = getMySQLConnection();
            cs = con.prepareCall("{ call proc_update_weather_data (?, ?, ?)}");
            cs.setString(1, "weather" + city);
            cs.setString(2, headline.replaceAll("\'", "''"));
            details = details.replaceAll("\'", "\"");
            details = details.replaceAll("\"", "\\\"");
            cs.setString(3, details);
            cs.execute();
            cs.close();
            log.info("Weather updated for (" + city + ") with (" + details + ") ");
        } catch (Exception e) {
            log.error("updateWeatherData(" + city + ", " + details + ") ", e);
        } finally {
            closeMySQLConnection(con);
        }
    }

    public void updateStockMarketRates(Vector vec) {
        Connection conn = null;
        CallableStatement cs = null;
        try {
            conn = getMySQLConnection();
            cs = conn.prepareCall("{ call proc_update_stocks (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
            for (int i = 0; i < vec.size(); i++) {
                String[] data = (String[]) vec.get(i);
                if (data.length == 9) {
                    int j = 1;
                    for (j = 1; j <= data.length; j++) {
                        cs.setString(j, data[j - 1]);
                    }
                    cs.setInt(j, (i == 0 ? 1 : 0));
                }
                cs.execute();
            }
            cs.close();
            log.info("Stock Market Rates updated");
        } catch (Exception e) {
            log.error("updateStockMarketRates() ", e);
        } finally {
            closeMySQLConnection(conn);
        }
    }

    public void updateStockIndexes(Vector vec) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getMySQLConnection();
            ps = conn.prepareStatement("replace INTO `stock_index` (`index`, `current`, `volume`, `value`, `high`, `low`, `change`) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?);");
            for (int i = 0; i < vec.size(); i++) {
                String[] data = (String[]) vec.get(i);
                if (data.length == 7) {
                    int j = 1;
                    for (j = 1; j <= data.length; j++) {
                        ps.setString(j, data[j - 1]);
                    }
                }
                ps.executeUpdate();
            }
            ps.close();
            log.info("Stock Market Indexes updated");
        } catch (Exception e) {
            log.error("updateStockIndexes() ", e);
        } finally {
            closeMySQLConnection(conn);
        }
    }

    public void updateHoroscope(String star, String text) {
        Connection con = null;
        try {
            con = getMySQLConnection();
            if (star.startsWith("love")) {
                updateLoveHoroscope(star.substring(star.indexOf("love-") + 5), text, con);
            } else {
                CallableStatement cs = null;
                cs = con.prepareCall("{ call proc_update_horoscope (?, ?)}");
                cs.setString(1, star);
                cs.setString(2, text.replaceAll("\'", "''"));
                cs.execute();
                cs.close();
                log.info("Horoscope updated for (" + star + ") with (" + text + ") ");
            }
        } catch (Exception e) {
            log.error("updateHoroscope(" + star + ", " + text + ") ", e);
        } finally {
            closeMySQLConnection(con);
        }

    }

    public void updateLoveHoroscope(String star, String text, Connection con) {
        PreparedStatement pst = null;
        try {
            String strQuery = "replace into love_horoscope(star,description) values(?,?)";
            pst = con.prepareStatement(strQuery);
            pst.setString(1, star);
            pst.setString(2, text.replaceAll("\'", "''"));
            pst.execute();
            pst.close();
            log.info("Love Horoscope updated for (" + star + ") with (" + text + ") ");
        } catch (Exception e) {
            log.error("update LoaveHoroscope(" + star + ", " + text + ") ", e);
        }
    }

    public void updateCricLiveScore(String team1, String team2, String smsText) {
        PreparedStatement pst = null;
        Connection con = null;
        try {
            con = getMySQLConnection();
//            String strQuery = "replace into cri_livescore(team1, team2, smstext, tstamp, status) values(?,?,?,now(),?)";
            String strQuery = "update cri_livescore set smstext=?, tstamp=now()";
            pst = con.prepareStatement(strQuery);
            int index=1;
//            pst.setString(index++, team1);
//            pst.setString(index++, team2);
            pst.setString(index++, smsText.replaceAll("\'", "''"));
//            pst.setInt(4, status);
            int uc = pst.executeUpdate();
//            if(uc == 0) {
//            	pst.clearParameters();
//                index=1;
//                pst.setString(index++, team1);
//                pst.setString(index++, team2);
//                pst.setString(index++, smsText.replaceAll("\'", "''"));
//                uc = pst.executeUpdate();
//            }
            pst.close();
            log.info("CricLiveScore update="+uc+" for (" + team1 + "_" + team2 + ") with (" + smsText + ")");
        } catch (Exception e) {
            log.error("updateCricLiveScore(" + team1 + "," + team2 + "," + smsText + ") ", e);
        } finally {
            closeMySQLConnection(con);
        }
    }

    public void updateCricEvent(String teamA, String teamB, String smsText, String eventId, String eventType){
        PreparedStatement pst = null;
        Connection con = null;
        try {
            con = getMySQLConnection();
            String strQuery = "";
            
            boolean isEndOfMatch = "END_OF_MATCH_EVENT".equals(eventId) && "MATCH_OVER".equals(eventType);
            if (isEndOfMatch){
            	//update match result
            	try {
	            	strQuery = "update crischedule as b, (" +
	            		"select a.id from crischedule a where a.teamA=? and a.teamB=? and a.status=1 order by a.id desc limit 1"+
	            		") as last_match set b.match_result=? where b.id = last_match.id";
	            	pst = con.prepareStatement(strQuery);
	                int index=1;
	                pst.setString(index++, smsText.replaceAll("\'", "''"));
	                pst.setString(index++, teamA);
	                pst.setString(index++, teamB);                
	                int uc = pst.executeUpdate();
	                if(uc == 0) {
	                	pst.clearParameters();
	                    index=1;
	                    pst.setString(index++, smsText.replaceAll("\'", "''"));
	                    pst.setString(index++, teamB);
	                    pst.setString(index++, teamA);
	                    uc = pst.executeUpdate();
	                    if(uc > 0)
	                    	log.info("Match Result updated for (" + teamB + "_" + teamA + ") with (" + smsText + ") ");
	                }
	                else {
	                	log.info("Match Result updated for (" + teamA + "_" + teamB + ") with (" + smsText + ") ");
	                }
	                pst.close();
            	} catch(Exception e) {
            		
            	} finally {
            		pst = null;
            	}
            }
            
            strQuery = "INSERT INTO `cric_events` (`teamA`, `teamB`, `smstext`, `eventid`, `eventtype`, tstamp) VALUES (?, ?, ?, ?, ?, now())";
            pst = con.prepareStatement(strQuery);
            int index=1;            
            pst.setString(index++, teamA);
            pst.setString(index++, teamB);
            pst.setString(index++, smsText.replaceAll("\'", "''"));
            pst.setString(index++, eventId.replaceAll("_", " "));
            pst.setString(index++, eventType.replaceAll("_", " "));
            int uc = pst.executeUpdate();
            if(uc > 0) {
            	log.info("CricEvent Added for (" + teamA + "_" + teamB + ") with (" + smsText + ") ");
            }
            pst.close();
            pst.close();            
        } catch (Exception e) {
            log.error("updateCricEvent(" + teamA + "," + teamB + "," + smsText + "," + eventId + "," + eventType + ") ", e);
        } finally {
        	closeMySQLConnection(con);
            pst = null;
        }
    }
    public Vector<String[]> getReSendWapPushUsers(){
        Connection con=null;
        Vector<String[]> v=new Vector<String[]>(); 
        try{
            String strQuery="SELECT seqid,content_id,category_type,msisdn,shortcode from content_charge where category_type <> 'Collstuff' and status=0 and date_format(tstamp,'%Y-%m-%d %H:%i') = date_Format(DATE_SUB(now(), INTERVAL 1 DAY), '%Y-%m-%d %H:%i') order by tstamp";
            con = dbProc.getMySQLConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            ResultSet rs = pst.executeQuery();
            while(rs.next()){
                v.add(new String[]{rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5)});
            }
            pst.close();
            rs.close();
        }catch(Exception e){
            log.error("getReSendWapPushUsers",e);
        }finally{
            dbProc.closeMySQLConnection(con);
        }
        return v;
    }
    public String [] getGreetingCardInfo(String cardId) {
        String [] cardInfo = null;
        Connection con = null;
        DBProc dbcon = DBProc.getInstance();
        try {            
            con = dbcon.getMySQLConnection();
            String strQuery="select gc.ContentId,"+
				"(select c.CategoryID from content c where c.contentid=gc.contentid limit 1) CategoryID,"+
				"(select c.CategoryName from content c where c.contentid=gc.contentid limit 1) CategoryName,"+
				"gc.Sender,gc.Rcv_MSISDN from greetingcardsendinfo gc where gc.seqid=?";
            PreparedStatement pst = con.prepareStatement(strQuery); 
            pst.setInt(1,Integer.parseInt(cardId.trim()));
            ResultSet rs = pst.executeQuery();
            //ContentId,Snd_MSISDN,Sender,Receiver,Rcv_MSISDN,DateAndTime,Status            
            
	        if(rs.next()) {
	        	cardInfo = new String[]{rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5)};
	        }
            rs.close();
            pst.close();
        } catch (Exception e) {
            log.error("getGreetingCardInfo(" + cardId + ") ", e);
        }
        finally{
            dbcon.closeMySQLConnection(con);
        }
        return cardInfo;
    }//getGreetingCardInfo
}
